﻿using System;
using System.Collections.Generic;
using System.Dynamic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Routing;
using QLGT_2.Models;

namespace QLGT_2.Controllers
{
    public class ThongKeController : Controller
    {
        QLGTContext db = new QLGTContext();

        // GET: ThongKe
        //public ActionResult Index()
        //{
        //    return View();
        //}

        #region Giaotrinh theo NXB
        public ActionResult ThKGiaoTrinhtheoNXB()
        {
            //ViewBag.NhaXuatBan = new SelectList(db.tbl_NhaXuatBans.ToList(), "MaNXB", "TenNXB");
            if (Request.IsAjaxRequest())
                return PartialView();
            else
                return View();
        }

        [HttpPost]
        [ActionName("ThKGiaoTrinhtheoNXB")]
        public ActionResult POST_ThKGiaoTrinhtheoNXB(string TuNgay, string DenNgay)
        {
            if (ModelState.IsValid)
            {
                //ViewBag.NhaXuatBan = db.tbl_NhaXuatBans.Where(e => e.MaNXB.ToString() == NhaXuatBan).First().TenNXB;

                ViewBag.TuNgay = TuNgay;
                ViewBag.DenNgay = DenNgay;
                DateTime _TuNgay = DateTime.Parse(TuNgay);
                DateTime _DenNgay = DateTime.Parse(DenNgay);

                var thkGiaoTrinh = from a in db.tbl_GiaoTrinhs
                                     join b in db.tbl_GiaoTrinh_Nams on a.MaGiaoTrinh equals b.MaGiaoTrinh
                                     join c in db.tbl_GiaoTrinh_TacGias on a.MaGiaoTrinh equals c.MaGiaoTrinh
                                     join d in db.tbl_TacGias on c.MaTacGia equals d.MaTacGia
                                     join e in db.tbl_HocHams on d.MaHocHam equals e.MaHocHam
                                     join f in db.tbl_HocVis on d.MaHocVi equals f.MaHocVi
                                     join g in db.tbl_NhaXuatBans on a.MaNXB equals g.MaNXB
                                     join h in db.tbl_GiaoTrinh_HeDTs on a.MaGiaoTrinh equals h.MaGiaoTrinh
                                     join i in db.tbl_HeDaoTaos on h.MaHeDaoTao equals i.MaHeDaoTao
                                     where (DateTime.Compare(a.HieuDinh, _TuNgay) >= 0 && DateTime.Compare(a.HieuDinh, _DenNgay) <= 0)
                                     select new kqthkGiaoTrinh
                                     {
                                         MaGiaoTrinh = a.MaGiaoTrinh,
                                         TenGiaoTrinh = a.TenGiaoTrinh,
                                         HieuDinh = a.HieuDinh,
                                         TenNXB = g.TenNXB,
                                         Nam = b.Nam,
                                         GiaNiemYet = b.GiaNiemYet,
                                         TenTacGia = d.TenTacGia,
                                         MaHocHam = d.MaHocHam,
                                         HocHam = e.HocHam,
                                         VTHocHam = e.VietTat,
                                         MaHocVi = d.MaHocVi,
                                         HocVi = f.HocVi,
                                         VTHocVi = f.VietTat,
                                         TenHeDaoTao = i.TenHeDaoTao,
                                         SoTrang = a.SoTrang
                                     };

                #region Gop Ma -> dsCuoi
                var dsPhu = thkGiaoTrinh.GroupBy(m => m.MaGiaoTrinh).Select(m => m.FirstOrDefault()).ToList();
                List<kqthkGiaoTrinh> dsGopMa = new List<kqthkGiaoTrinh>();
                foreach (var a in dsPhu)
                {
                    string tacGia = "";
                    foreach (var b in thkGiaoTrinh)
                    {
                        if (b.MaGiaoTrinh.Trim() == a.MaGiaoTrinh.Trim())
                        {
                            if (b.HocHam != "Không có")
                            {
                                tacGia += b.VTHocHam + ". TS. " + b.TenTacGia + "; ";
                            }
                            else if (b.HocVi != "Không có")
                            {
                                tacGia += b.VTHocVi + ". " + b.TenTacGia + "; ";
                            }
                            else
                            {
                                tacGia += b.TenTacGia + "; ";
                            }
                        }
                    }
                    tacGia = tacGia.Substring(0, tacGia.Length - 2).Trim();
                    dsGopMa.Add(new kqthkGiaoTrinh()
                    {
                        MaGiaoTrinh = a.MaGiaoTrinh,
                        TenGiaoTrinh = a.TenGiaoTrinh,
                        HieuDinh = a.HieuDinh,
                        TenNXB = a.TenNXB,
                        Nam = a.Nam,
                        GiaNiemYet = a.GiaNiemYet,
                        SoTrang = a.SoTrang,
                        TenTacGia = tacGia,
                        TenHeDaoTao = a.TenHeDaoTao
                    });
                }
                #endregion

                #region Gop Nam + Gia niem yet -> dsCuoi
                var dsBT = from a in dsGopMa
                           group a by a.MaGiaoTrinh into b
                           let c = b.FirstOrDefault()
                           select new kqthkGiaoTrinh
                           {
                               MaGiaoTrinh = c.MaGiaoTrinh,
                               TenGiaoTrinh = c.TenGiaoTrinh,
                               HieuDinh = c.HieuDinh,
                               TenNXB = c.TenNXB,
                               Nam = b.Max(m => m.Nam),
                               GiaNiemYet = c.GiaNiemYet,
                               SoTrang = c.SoTrang,
                               TenTacGia = c.TenTacGia,
                               TenHeDaoTao = c.TenHeDaoTao
                           };
                #endregion

                return PartialView("_ThKGiaoTrinhtheoNXB", dsBT.ToList());
            }
            else
                ModelState.AddModelError("Error", "Có lỗi.");

            return PartialView();
        }
        #endregion

        #region Ban - Dat - Ton
        public ActionResult ThKBanDatTon()
        {
            if (Request.IsAjaxRequest())
                return PartialView();
            else
                return View();
        }

        [HttpPost]
        [ActionName("ThKBanDatTon")]
        public ActionResult POST_ThKBanDatTon(string Nam)
        {
            if (ModelState.IsValid)
            {
                //ViewBag.Nam = Nam;
                int nam = int.Parse(Nam);

                var thkBan = from a in db.tbl_SachBans where (a.NgayBan.Year == nam)
                                select new kqthkBanDatTon
                                {
                                    MaGiaoTrinh = a.MaGiaoTrinh,
                                    SoLuongBan = a.SoLuong
                                };

                var thkDat = from a in db.tbl_SachDats where (a.NgayDat.Year == nam)
                             select new kqthkBanDatTon
                             {
                                 MaGiaoTrinh = a.MaGiaoTrinh,
                                 SoLuongDat = a.SoLuong
                             };

                var thkTon = from a in db.tbl_SachTons where (a.Nam == nam)
                             select new kqthkBanDatTon
                             {
                                 MaGiaoTrinh = a.MaGiaoTrinh,
                                 SoLuongTon = a.SoLuong
                             };

                int tongDat = 0, tongBan = 0, tongTon=0;
                if (thkDat.Count() !=0)
                {
                    foreach (var item in thkDat)
                    {
                        tongDat += item.SoLuongDat;
                    }
                }
                if (thkBan.Count() != 0)
                {
                    foreach (var item in thkBan)
                    {
                        tongBan += item.SoLuongBan;
                    }
                }
                if (thkTon.Count() !=0)
                {
                    tongTon = thkTon.First().SoLuongTon;
                }

                List<kqthkBanDatTon> dsCuoi = new List<kqthkBanDatTon>();
                dsCuoi.Add(new kqthkBanDatTon()
                    {
                        SoLuongBan = tongBan,
                        SoLuongDat = tongDat,
                        SoLuongTon = tongTon,
                        Nam = nam   
                    });

                return PartialView("_ThKBanDatTon", dsCuoi.ToList());
            }
            else
                ModelState.AddModelError("Error", "Có lỗi.");

            return PartialView();
        }
        #endregion
    }
}